In [ ]:
import pandas as pd
import matplotlib.pyplot as plt
import descartes
import geopandas as gpd
from shapely.geometry import Point, Polygon
import folium

%matplotlib inline
In [ ]:
df = pd.read_json(path_or_buf="sign_ins_formatted.json")
df
Out[ ]:
id createdDateTime userDisplayName userPrincipalName userId appId appDisplayName ipAddress ipAddressFromResourceProvider clientAppUsed ... authenticationContextClassReferences authenticationProcessingDetails networkLocationDetails authenticationDetails authenticationRequirementPolicies sessionLifetimePolicies privateLinkDetails appliedEventListeners authenticationAppPolicyEvaluationDetails managedServiceIdentity
0 a24a547c-71e2-47fc-97bb-81d7e4d5aa00 2024-04-06T16:44:27Z a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa c44b4083-3bb0-49c1-b47d-974e53cbdf3c Azure Portal 69.141.220.21 NaN ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [] [] [] {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No...
1 bd5a69d1-0a15-42e0-90da-073377702e00 2024-04-06T16:44:24Z Tyler Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa c44b4083-3bb0-49c1-b47d-974e53cbdf3c Azure Portal 69.141.220.21 NaN ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [{'authenticationStepDateTime': '2024-04-06T16... [] [] {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'Number Match', 'adminConfigur... {'msiType': 'none', 'associatedResourceId': No...
2 452d6571-4dba-4ea5-8acf-777ac906b300 2024-04-06T16:44:17Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa 4813382a-8fa7-425e-ab75-3b753aab3abb Microsoft Authenticator App 69.141.220.21 NaN Mobile Apps and Desktop clients ... [] [{'key': 'Root Key Type', 'value': 'Unknown'},... [] [] [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No...
3 c4827fe9-ee3a-4d1e-a320-d166620aa500 2024-04-06T16:40:46Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa af124e86-4e96-495a-b70a-90f90ab96707 OneDrive iOS App 69.141.220.21 NaN Mobile Apps and Desktop clients ... [] [{'key': 'Root Key Type', 'value': 'Unknown'},... [] [] [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No...
4 d87909c1-c089-4336-be59-fe56ce5ba600 2024-04-06T16:39:31Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa de8bc8b5-d9f9-48b1-a8ad-b748da725064 Graph Explorer 69.141.220.21 NaN Browser ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [] [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5297 f495a8aa-2cf1-449e-afed-2579fc408600 2024-03-07T18:31:20Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [{'authenticationStepDateTime': '2024-03-07T18... [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No...
5298 937f1245-de51-4abb-882c-367019823700 2024-03-07T18:31:03Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [{'authenticationStepDateTime': '2024-03-07T18... [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No...
5299 f495a8aa-2cf1-449e-afed-2579d0358600 2024-03-07T18:30:46Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... [] [{'key': 'Root Key Type', 'value': 'Unknown'}] [] [{'authenticationStepDateTime': '2024-03-07T18... [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No...
5300 0890237d-aeae-464d-905b-17908bc7b400 2024-03-07T18:28:12Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa af124e86-4e96-495a-b70a-90f90ab96707 OneDrive iOS App 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Mobile Apps and Desktop clients ... [] [{'key': 'Root Key Type', 'value': 'Unknown'},... [] [] [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No...
5301 3cb8a923-341a-4d63-8b00-7cc108384f01 2024-03-07T18:09:45Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa 27922004-5251-4030-b22d-91ecd9a37ea4 Outlook Mobile 108.5.169.55 NaN Mobile Apps and Desktop clients ... [] [{'key': 'Root Key Type', 'value': 'Unknown'},... [] [] [] [{'expirationRequirement': 'rememberMultifacto... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No...

5302 rows × 66 columns

In [ ]:
df.columns.to_list()
Out[ ]:
['id',
 'createdDateTime',
 'userDisplayName',
 'userPrincipalName',
 'userId',
 'appId',
 'appDisplayName',
 'ipAddress',
 'ipAddressFromResourceProvider',
 'clientAppUsed',
 'userAgent',
 'correlationId',
 'conditionalAccessStatus',
 'originalRequestId',
 'isInteractive',
 'tokenIssuerName',
 'tokenIssuerType',
 'clientCredentialType',
 'processingTimeInMilliseconds',
 'riskDetail',
 'riskLevelAggregated',
 'riskLevelDuringSignIn',
 'riskState',
 'riskEventTypes_v2',
 'resourceDisplayName',
 'resourceId',
 'resourceTenantId',
 'homeTenantId',
 'homeTenantName',
 'authenticationMethodsUsed',
 'authenticationRequirement',
 'signInIdentifier',
 'signInIdentifierType',
 'servicePrincipalName',
 'signInEventTypes',
 'servicePrincipalId',
 'federatedCredentialId',
 'userType',
 'flaggedForReview',
 'isTenantRestricted',
 'autonomousSystemNumber',
 'crossTenantAccessType',
 'servicePrincipalCredentialKeyId',
 'servicePrincipalCredentialThumbprint',
 'uniqueTokenIdentifier',
 'incomingTokenType',
 'authenticationProtocol',
 'resourceServicePrincipalId',
 'signInTokenProtectionStatus',
 'originalTransferMethod',
 'mfaDetail',
 'authenticationAppDeviceDetails',
 'status',
 'deviceDetail',
 'location',
 'appliedConditionalAccessPolicies',
 'authenticationContextClassReferences',
 'authenticationProcessingDetails',
 'networkLocationDetails',
 'authenticationDetails',
 'authenticationRequirementPolicies',
 'sessionLifetimePolicies',
 'privateLinkDetails',
 'appliedEventListeners',
 'authenticationAppPolicyEvaluationDetails',
 'managedServiceIdentity']
In [ ]:
locations = df["location"]
locations[1]["geoCoordinates"]
Out[ ]:
{'altitude': None, 'latitude': 40.35863, 'longitude': -74.13705}
In [ ]:
city = locations.apply(lambda x: x["city"])
state = locations.apply(lambda x: x["state"])
countryOrRegion = locations.apply(lambda x: x["countryOrRegion"])
altitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
    lambda x: x["altitude"])
latitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
    lambda x: x["latitude"])
longitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
    lambda x: x["longitude"]
)

df["city"] = city
df["state"] = state
df["countryOrRegion"] = countryOrRegion
df["altitude"] = altitude
df["latitude"] = latitude
df["longitude"] = longitude


df
Out[ ]:
id createdDateTime userDisplayName userPrincipalName userId appId appDisplayName ipAddress ipAddressFromResourceProvider clientAppUsed ... privateLinkDetails appliedEventListeners authenticationAppPolicyEvaluationDetails managedServiceIdentity city state countryOrRegion altitude latitude longitude
0 a24a547c-71e2-47fc-97bb-81d7e4d5aa00 2024-04-06T16:44:27Z a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa c44b4083-3bb0-49c1-b47d-974e53cbdf3c Azure Portal 69.141.220.21 NaN ... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No... None NaN NaN
1 bd5a69d1-0a15-42e0-90da-073377702e00 2024-04-06T16:44:24Z Tyler Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa c44b4083-3bb0-49c1-b47d-974e53cbdf3c Azure Portal 69.141.220.21 NaN ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'Number Match', 'adminConfigur... {'msiType': 'none', 'associatedResourceId': No... Lincroft New Jersey US None 40.35863 -74.13705
2 452d6571-4dba-4ea5-8acf-777ac906b300 2024-04-06T16:44:17Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa 4813382a-8fa7-425e-ab75-3b753aab3abb Microsoft Authenticator App 69.141.220.21 NaN Mobile Apps and Desktop clients ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No... Lincroft New Jersey US None 40.35863 -74.13705
3 c4827fe9-ee3a-4d1e-a320-d166620aa500 2024-04-06T16:40:46Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa af124e86-4e96-495a-b70a-90f90ab96707 OneDrive iOS App 69.141.220.21 NaN Mobile Apps and Desktop clients ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No... Lincroft New Jersey US None 40.35863 -74.13705
4 d87909c1-c089-4336-be59-fe56ce5ba600 2024-04-06T16:39:31Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa de8bc8b5-d9f9-48b1-a8ad-b748da725064 Graph Explorer 69.141.220.21 NaN Browser ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No... Lincroft New Jersey US None 40.35863 -74.13705
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5297 f495a8aa-2cf1-449e-afed-2579fc408600 2024-03-07T18:31:20Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No... Freehold New Jersey US None 40.27322 -74.23018
5298 937f1245-de51-4abb-882c-367019823700 2024-03-07T18:31:03Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No... Freehold New Jersey US None 40.27322 -74.23018
5299 f495a8aa-2cf1-449e-afed-2579d0358600 2024-03-07T18:30:46Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa a81d90ac-aa75-4cf8-b14c-58bf348528fe Microsoft Community v2 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Browser ... {'policyId': '', 'policyName': '', 'resourceId... [] [] {'msiType': 'none', 'associatedResourceId': No... Freehold New Jersey US None 40.27322 -74.23018
5300 0890237d-aeae-464d-905b-17908bc7b400 2024-03-07T18:28:12Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa af124e86-4e96-495a-b70a-90f90ab96707 OneDrive iOS App 2600:1001:b112:cbee:8c5f:2fad:5454:f48e NaN Mobile Apps and Desktop clients ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No... Freehold New Jersey US None 40.27322 -74.23018
5301 3cb8a923-341a-4d63-8b00-7cc108384f01 2024-03-07T18:09:45Z Tyler M. Neher tyler@neherdata.com a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa 27922004-5251-4030-b22d-91ecd9a37ea4 Outlook Mobile 108.5.169.55 NaN Mobile Apps and Desktop clients ... {'policyId': '', 'policyName': '', 'resourceId... [] [{'policyName': 'App Lock', 'adminConfiguratio... {'msiType': 'none', 'associatedResourceId': No... Hazlet Township New Jersey US None 40.42598 -74.16610

5302 rows × 72 columns

In [ ]:
worldmap = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))

# Creating axes and plotting world map
fig, ax = plt.subplots(figsize=(16, 10))
worldmap.plot(color="lightgrey", ax=ax)

x = df["longitude"]
y = df["latitude"]
plt.scatter(x, y, alpha=0.6, cmap="autumn")
# plt.colorbar(label='NeherData.com Tenant Microsoft Entra ID Login Attempts')

# Creating axis limits and title
plt.xlim([-180, 180])
plt.ylim([-90, 90])

plt.title("NeherData.com Tenant Microsoft Entra ID Login Attempts")
plt.xlabel("Longitude")
plt.ylabel("Latitude")
plt.show()

/var/folders/m4/r3wttq2j5hs_1mpf0t1yvhq80000gn/T/ipykernel_76624/2671292473.py:1: FutureWarning: The geopandas.dataset module is deprecated and will be removed in GeoPandas 1.0. You can get the original 'naturalearth_lowres' data from https://www.naturalearthdata.com/downloads/110m-cultural-vectors/.
  worldmap = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
/var/folders/m4/r3wttq2j5hs_1mpf0t1yvhq80000gn/T/ipykernel_76624/2671292473.py:9: UserWarning: No data for colormapping provided via 'c'. Parameters 'cmap' will be ignored
  plt.scatter(x, y, alpha=0.6, cmap="autumn")
No description has been provided for this image
In [ ]:
# Make an empty map
m = folium.Map(location=[20, 0], tiles="CartoDB Voyager", zoom_start=2)

# Show the map
m
Out[ ]:
Make this Notebook Trusted to load map: File -> Trust Notebook
In [ ]:
latlon_df = pd.DataFrame(
    data=df.dropna(subset=["latitude", "longitude"]),
    columns=["latitude", "longitude", "city", "state", "countryOrRegion"],
)
# latlon_df['latitude'] = df['latitude'].notna()


latlon_df
Out[ ]:
latitude longitude city state countryOrRegion
1 40.35863 -74.13705 Lincroft New Jersey US
2 40.35863 -74.13705 Lincroft New Jersey US
3 40.35863 -74.13705 Lincroft New Jersey US
4 40.35863 -74.13705 Lincroft New Jersey US
5 40.35863 -74.13705 Lincroft New Jersey US
... ... ... ... ... ...
5297 40.27322 -74.23018 Freehold New Jersey US
5298 40.27322 -74.23018 Freehold New Jersey US
5299 40.27322 -74.23018 Freehold New Jersey US
5300 40.27322 -74.23018 Freehold New Jersey US
5301 40.42598 -74.16610 Hazlet Township New Jersey US

5295 rows × 5 columns

In [ ]:
# add marker one by one on the map
for i in range(0, len(latlon_df)):
    folium.Marker(
        location=[latlon_df.iloc[i]["latitude"],
                  latlon_df.iloc[i]["longitude"]],
        popup=latlon_df.iloc[i]["city"],
    ).add_to(m)

# Show the map again
m
Out[ ]:
Make this Notebook Trusted to load map: File -> Trust Notebook
In [ ]:
map_html = m._repr_html_()